【MySQL】六、条件查询(where,is null和is not null ,and,or) |
您所在的位置:网站首页 › sql查询 where › 【MySQL】六、条件查询(where,is null和is not null ,and,or) |
文章目录
1. 回顾2. 案例2.1 找出哪些人没有津贴2.2 找出哪些人有津贴2.3 找出工作岗位是MANAGER 和 SALESMAN的员工2.4 找出薪资大于3000并且部门编号是20或者30的部门员工(and 和 or 联合使用)
1. 回顾
在上一篇博客中【MySQL】五、条件查询(where,between … and 关键字) ,我们知道,sql查询语句的语法为: select 字段1, 字段2,..,字段n from 表名 where 条件语句; 2. 案例 2.1 找出哪些人没有津贴我们先查看一下员工表结构; desc emp; # 查看表的详细结构;字段COMM为津贴 +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | EMPNO | int(4) | NO | PRI | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | int(4) | YES | | NULL | | | HIREDATE | date | YES | | NULL | | | SAL | double(7,2) | YES | | NULL | | | COMM | double(7,2) | YES | | NULL | | # 津贴 | DEPTNO | int(2) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 8 rows in set (0.00 sec)只查询所有津贴; select comm from emp ; mysql> select comm from emp ; +---------+ | comm | +---------+ | NULL | | 300.00 | | 500.00 | | NULL | | 1400.00 | | NULL | | NULL | | NULL | | NULL | | 0.00 | | NULL | | NULL | | NULL | | NULL | +---------+ 14 rows in set (0.00 sec)我们发现津贴有的为NULL,有的为0.0; 在数据库中NULL不是一个值,它表示什么也没有,为空。空不是一个值,不能用等号衡量。 必须使用 is null 或者 is not null; 因此: select ename, comm from emp where comm = 0 or comm is null;查询结果: +--------+---------+------+ | ename | sal | comm | +--------+---------+------+ | SMITH | 800.00 | NULL | | JONES | 2975.00 | NULL | | BLAKE | 2850.00 | NULL | | CLARK | 2450.00 | NULL | | SCOTT | 3000.00 | NULL | | KING | 5000.00 | NULL | | TURNER | 1500.00 | 0.00 | | ADAMS | 1100.00 | NULL | | JAMES | 950.00 | NULL | | FORD | 3000.00 | NULL | | MILLER | 1300.00 | NULL | +--------+---------+------+ 11 rows in set (0.00 sec) 2.2 找出哪些人有津贴 select ename,sal, comm from emp where comm is not null;查询结果: +--------+---------+---------+ | ename | sal | comm | +--------+---------+---------+ | ALLEN | 1600.00 | 300.00 | | WARD | 1250.00 | 500.00 | | MARTIN | 1250.00 | 1400.00 | | TURNER | 1500.00 | 0.00 | +--------+---------+---------+ 4 rows in set (0.00 sec) 2.3 找出工作岗位是MANAGER 和 SALESMAN的员工 select ename, job from emp where job = 'manager' or job = 'salesman';查询结果: +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+ 7 rows in set (0.00 sec)注意: 这里要使用or关键字而不是and, 使用and表示岗位既是MANAGER,又是SALESMAN。 2.4 找出薪资大于3000并且部门编号是20或者30的部门员工(and 和 or 联合使用)and 的优先级是高于or的,因此需要将后面带or的条件语句使用括号 select ename, sal, deptno from emp where sal > 3000 and (deptno = 20 or 30);查询结果: +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | KING | 5000.00 | 10 | +-------+---------+--------+ 1 row in set (0.00 sec)如果不使用括号,则: sal > 3000 and deptno 会配对,导致部门编号为30的员工都被查询出来。 select ename, sal, deptno from emp where sal > 3000 and deptno = 20 or 30; # 错误写法 +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | SMITH | 800.00 | 20 | | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | CLARK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | JAMES | 950.00 | 30 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | +--------+---------+--------+ 14 rows in set (0.00 sec)注意: 当运算符的优先级不确定时,请加小括号! |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |